#include "databasemanager.h"
#include "../constants.h"

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>
#include <QSqlRecord>

namespace Jcr
{
class DatabaseManagerPrivate
{
public:
    void initialize();
    bool isInitialized() const;
    void createBrandModelTable();
    void createModelVideoLinkTable();
    void createModelVideoTrueLinkTable();
    void createModelParamsTable();

    QSqlDatabase database;
    bool isInit = false;

};

void DatabaseManagerPrivate::initialize()
{
    if (!isInit) {
        database = QSqlDatabase::addDatabase("QSQLITE");
        database.setDatabaseName("./jcrspider.db");
        isInit = database.open();
        if (!isInit)
            qWarning() << "Failed to open database: " << database.lastError();
    }
}

bool DatabaseManagerPrivate::isInitialized() const
{
    return isInit;
}

DatabaseManager::DatabaseManager(QObject *parent) : QObject(parent)
{
    d_ptr = new DatabaseManagerPrivate;
}

DatabaseManager::~DatabaseManager()
{
    delete d_ptr;
}

void DatabaseManagerPrivate::createBrandModelTable()
{
    QString strQuery = QString("CREATE TABLE IF NOT EXISTS %1 (brand_name VARCHAR(50) NOT NULL, model_name VARCHAR(50) NOT NULL, PRIMARY KEY(model_name))").arg(Jcr::Database::BRAND_MODEL_TABLE);
    database.exec(strQuery);
}

void DatabaseManagerPrivate::createModelVideoLinkTable()
{
    QString strQuery = QString("CREATE TABLE IF NOT EXISTS %1 (model_name VARCHAR(50) NOT NULL, video_link VARCHAR(500) NOT NULL, PRIMARY KEY(video_link))").arg(Jcr::Database::MODEL_VIDEOLINK_TABLE);
    database.exec(strQuery);
}

void DatabaseManagerPrivate::createModelVideoTrueLinkTable()
{
    QString strQuery = QString("CREATE TABLE IF NOT EXISTS %1 (model_name VARCHAR(50) NOT NULL, video_link VARCHAR(500) NOT NULL, PRIMARY KEY(video_link))").arg(Jcr::Database::MODEL_VIDEOLINK_REAL_TABLE);
    database.exec(strQuery);
}

void DatabaseManagerPrivate::createModelParamsTable()
{
    /// column names are:
    // release_date
    // emission_volume
    // manufacture_state
    // cylinder_count
    // length_width_height
    // travel
    // shaft_distance
    // cooling_style
    // seat_height
    // compression_ratio
    // break_style
    // max_capacity
    // land_distance
    // max_torsion
    // empty_weight
    // gas_supply_style
    // full_weight
    // economical_gas_cost
    // max_speed
    // env_protection_standard
    // wheel_count
    // ignition_style
    // wheel_type
    // clutch_type
    // front_wheel_standard
    // transmission_type
    // back_wheel_standard
    // colors
    // gas_volume
    QString strQuery = QString("CREATE TABLE IF NOT EXISTS %1 (model_name VARCHAR(50) NOT NULL,"
                               "release_date VARCHAR(20), emission_volume VARCHAR(10),"
                               "manufacture_state VARCHAR(10), cylinder_count VARCHAR(5),"
                               "length_width_height VARCHAR(30), travel VARCHAR(10),"
                               "shaft_distance VARCHAR(10), cooling_style VARCHAR(10),"
                               "seat_height VARCHAR(10), compression_ratio VARCHAR(10),"
                               "break_style VARCHAR(10), max_capacity VARCHAR(10),"
                               "land_distance VARCHAR(10), max_torsion VARCHAR(10), "
                               "empty_weight VARCHAR(10), gas_supply_style VARCHAR(20),"
                               "full_weight VARCHAR(10), economical_gas_cost VARCHAR(10),"
                               "max_speed VARCHAR(10), env_protection_standard VARCHAR(10),"
                               "wheel_count VARCHAR(10), ignition_style VARCHAR(10),"
                               "wheel_type VARCHAR(10), clutch_type VARCHAR(10),"
                               "front_wheel_standard VARCHAR(20), transmission_type VARCHAR(20),"
                               "back_wheel_standard VARCHAR(20), colors VARCHAR(50),"
                               "gas_volume VARCHAR(10),PRIMARY KEY (model_name))").arg(Jcr::Database::MODEL_PARAMS_TABLE);
    database.exec(strQuery);
}

DatabaseManager* DatabaseManager::instance()
{
    static DatabaseManager db;
    return &db;
}

bool DatabaseManager::addModelParams(const QString& modelName, const QStringList& params)
{
    if (!d_ptr->isInitialized())
        d_ptr->initialize();
    d_ptr->createModelParamsTable();

    QString strQuery = QString("INSERT INTO %1 VALUES(\"%2\",").arg(Jcr::Database::MODEL_PARAMS_TABLE).arg(modelName);
    foreach (auto param, params)
        strQuery += QString("\"%1\",").arg(param);
    strQuery.chop(1);
    strQuery += ")";
    QSqlQuery query = d_ptr->database.exec(strQuery);
    bool success = query.numRowsAffected() > 0;
    return success;
}

bool DatabaseManager::updateModelParams(const QString& modelName, const QStringList& params)
{
    Q_ASSERT(params.size() == 34);

    if (d_ptr->isInitialized())
        d_ptr->initialize();

    QString strAssign;
    strAssign += QString("release_date=\"%1\",").arg(params.at(0));
    strAssign += QString("emission_volume=\"%1\",").arg(params.at(1));
    strAssign += QString("manufacture_state=\"%1\",").arg(params.at(2));
    strAssign += QString("cylinder_count=\"%1\",").arg(params.at(3));
    strAssign += QString("length_width_height=\"%1\",").arg(params.at(4));
    strAssign += QString("travel=\"%1\",").arg(params.at(5));
    strAssign += QString("shaft_distance=\"%1\",").arg(params.at(6));
    strAssign += QString("cooling_style=\"%1\",").arg(params.at(7));
    strAssign += QString("seat_height=\"%1\",").arg(params.at(8));
    strAssign += QString("break_style=\"%1\",").arg(params.at(9));
    strAssign += QString("max_capacity=\"%1\",").arg(params.at(10));
    strAssign += QString("land_distance=\"%1\",").arg(params.at(11));
    strAssign += QString("max_torsion=\"%1\",").arg(params.at(12));
    strAssign += QString("empty_weight=\"%1\",").arg(params.at(13));
    strAssign += QString("gas_supply_style=\"%1\",").arg(params.at(14));
    strAssign += QString("full_weight=\"%1\",").arg(params.at(15));
    strAssign += QString("economical_gas_cost=\"%1\",").arg(params.at(16));
    strAssign += QString("max_speed=\"%1\",").arg(params.at(17));
    strAssign += QString("env_protection_standard=\"%1\",").arg(params.at(18));
    strAssign += QString("wheel_count=\"%1\",").arg(params.at(19));
    strAssign += QString("ignition_style=\"%1\",").arg(params.at(20));
    strAssign += QString("wheel_type=\"%1\",").arg(params.at(21));
    strAssign += QString("clutch_type=\"%1\",").arg(params.at(22));
    strAssign += QString("front_wheel_standard=\"%1\",").arg(params.at(23));
    strAssign += QString("transmission_type=\"%1\",").arg(params.at(24));
    strAssign += QString("back_wheel_standard=\"%1\",").arg(params.at(25));
    strAssign += QString("release_date=\"%1\",").arg(params.at(26));
    strAssign += QString("colors=\"%1\",").arg(params.at(27));
    strAssign += QString("gas_volume=\"%1\",").arg(params.at(28));
    strAssign += QString("country=\"%1\",").arg(params.at(29));
    strAssign += QString("cn_yuan=\"%1\",").arg(params.at(30));
    strAssign += QString("us_dollars=\"%1\",").arg(params.at(31));
    strAssign += QString("eu_euros=\"%1\",").arg(params.at(32));
    strAssign += QString("type=\"%1\"").arg(params.at(33));

    QString strQuery = QString("UPDATE %1 SET %2 WHERE model_name=\"%3\"").arg(Jcr::Database::MODEL_PARAMS_TABLE)
            .arg(strAssign).arg(modelName);

    QSqlQuery query;
    query.prepare(strQuery);
    query.exec();
    bool ok = query.numRowsAffected() > 0;
    return ok;
}

QStringList DatabaseManager::modelParams(const QString &modelName)
{
    if (modelName.isEmpty())
        return QStringList();

    if (!d_ptr->isInitialized())
        d_ptr->initialize();

    QString strQuery = QString("SELECT * FROM %1 WHERE model_name=\"%2\"").arg(Jcr::Database::MODEL_PARAMS_TABLE).arg(modelName);
    QSqlQuery query;
    query.prepare(strQuery);

    QStringList params;

    if (query.exec()) {
        QSqlRecord rec = query.record();
        while(query.next()) {
            // the first is model_name, ignore it
            for (int i = 1; i < rec.count(); i++)
                params.append(query.value(i).toString());
        }
    }
    return params;
}

bool DatabaseManager::setModelCnPrice(const QString& modelName, const QString& price)
{
    if (!d_ptr->isInitialized())
        d_ptr->initialize();

    QString strAssign = QString("cn_yuan=\"%1\"").arg(price);
    QString strQuery = QString("UPDATE %1 SET %2 WHERE model_name=\"%3\"").arg(Jcr::Database::MODEL_PARAMS_TABLE)
            .arg(strAssign).arg(modelName);

    QSqlQuery query;
    query.prepare(strQuery);
    query.exec();
    bool ok = query.numRowsAffected() > 0;
    return ok;
}

bool DatabaseManager::setModelUsPrice(const QString& modelName, const QString& price)
{
    if (!d_ptr->isInitialized())
        d_ptr->initialize();

    QString strAssign = QString("us_dollars=\"%1\"").arg(price);
    QString strQuery = QString("UPDATE %1 SET %2 WHERE model_name=\"%3\"").arg(Jcr::Database::MODEL_PARAMS_TABLE)
            .arg(strAssign).arg(modelName);

    QSqlQuery query;
    query.prepare(strQuery);
    query.exec();
    bool ok = query.numRowsAffected() > 0;
    return ok;
}

bool DatabaseManager::setModelEuPrice(const QString& modelName, const QString& price)
{
    if (!d_ptr->isInitialized())
        d_ptr->initialize();

    QString strAssign = QString("eu_euros=\"%1\"").arg(price);
    QString strQuery = QString("UPDATE %1 SET %2 WHERE model_name=\"%3\"").arg(Jcr::Database::MODEL_PARAMS_TABLE)
            .arg(strAssign).arg(modelName);

    QSqlQuery query;
    query.prepare(strQuery);
    query.exec();
    bool ok = query.numRowsAffected() > 0;
    return ok;
}

bool DatabaseManager::setModelCountry(const QString& modelName, const QString& country)
{
    if (!d_ptr->isInitialized())
        d_ptr->initialize();

    QString strAssign = QString("country=\"%1\"").arg(country);
    QString strQuery = QString("UPDATE %1 SET %2 WHERE model_name=\"%3\"").arg(Jcr::Database::MODEL_PARAMS_TABLE)
            .arg(strAssign).arg(modelName);

    QSqlQuery query;
    query.prepare(strQuery);
    query.exec();
    bool ok = query.numRowsAffected() > 0;
    return ok;
}

bool DatabaseManager::setModelType(const QString& modelName, const QString& type)
{
    if (!d_ptr->isInitialized())
        d_ptr->initialize();

    QString strAssign = QString("type=\"%1\"").arg(type);
    QString strQuery = QString("UPDATE %1 SET %2 WHERE model_name=\"%3\"").arg(Jcr::Database::MODEL_PARAMS_TABLE)
            .arg(strAssign).arg(modelName);

    QSqlQuery query;
    query.prepare(strQuery);
    query.exec();
    bool ok = query.numRowsAffected() > 0;
    return ok;
}

bool DatabaseManager::addBrandModel(const QString& brandName, const QString& model)
{
    if (!d_ptr->isInitialized())
        d_ptr->initialize();

    d_ptr->createBrandModelTable();
    QString strQuery = QString("INSERT INTO %1 VALUES(\"%2\", \"%3\")").arg(Jcr::Database::BRAND_MODEL_TABLE).arg(brandName).arg(model);
    QSqlQuery query = d_ptr->database.exec(strQuery);
    return query.numRowsAffected() > 0;
}

bool DatabaseManager::addModelVideoLink(const QString& model, const QString& link)
{
    if (!d_ptr->isInitialized())
        d_ptr->initialize();
    d_ptr->createModelVideoLinkTable();
    QString strQuery = QString("INSERT INTO %1 VALUES(\"%2\", \"%3\")").arg(Jcr::Database::MODEL_VIDEOLINK_TABLE).arg(model).arg(link);
    QSqlQuery query = d_ptr->database.exec(strQuery);
    return query.numRowsAffected() > 0;
}

bool DatabaseManager::removeModelVideoLink(const QString& link)
{
    if (!d_ptr->isInitialized())
        d_ptr->initialize();
    d_ptr->createModelVideoLinkTable();

    QString strQuery = QString("DELETE * FROM %1 WHERE video_link=\"%2\"").arg(Jcr::Database::MODEL_VIDEOLINK_TABLE).arg(link);
    QSqlQuery query;
    query.prepare(strQuery);
    query.exec();
    bool ok = query.numRowsAffected() > 0;
    return ok;
}

bool DatabaseManager::addModelVideoTrueLink(const QString& model, const QString& link)
{
    if (!d_ptr->isInitialized())
        d_ptr->initialize();
    d_ptr->createModelVideoTrueLinkTable();
    QString strQuery = QString("INSERT INTO %1 VALUES(\"%2\", \"%3\")").arg(Jcr::Database::MODEL_VIDEOLINK_REAL_TABLE).arg(model).arg(link);
    QSqlQuery query = d_ptr->database.exec(strQuery);
    return query.numRowsAffected() > 0;
}

bool DatabaseManager::removeModelVideoTrueLink(const QString& link)
{
    if (!d_ptr->isInitialized())
        d_ptr->initialize();
    d_ptr->createModelVideoTrueLinkTable();

    QString strQuery = QString("DELETE * FROM %1 WHERE video_link=\"%2\"").arg(Jcr::Database::MODEL_VIDEOLINK_REAL_TABLE).arg(link);
    QSqlQuery query;
    query.prepare(strQuery);
    query.exec();
    bool ok = query.numRowsAffected() > 0;
    return ok;

}

QStringList DatabaseManager::modelVideoLinks(const QString& modelName)
{
    if (!d_ptr->isInitialized())
        d_ptr->initialize();

    QString strQuery = QString("SELECT video_link FROM %1 WHERE model_name=\"%2\"").arg(Jcr::Database::MODEL_VIDEOLINK_REAL_TABLE).arg(modelName);
    QSqlQuery query;
    query.prepare(strQuery);
    query.exec();

    QStringList links;
    while(query.next()) {
        // the first is model_name, ignore it
        links.append(query.value(0).toString());
    }

    return links;
}

QStringList DatabaseManager::modelNewsLinks(const QString& modelName)
{
    return QStringList();
}

QStringList DatabaseManager::modelsWithVideo() const
{
    if (!d_ptr->isInitialized())
        d_ptr->initialize();

    QString strQuery = QString("SELECT DISTINCT model_name FROM %1 ").arg(Jcr::Database::MODEL_VIDEOLINK_TABLE);
    QSqlQuery query;
    query.prepare(strQuery);
    query.exec();

    QStringList models;
    while (query.next())
        models.append(query.value(0).toString());
    return models;
}


QString DatabaseManager::brandName(const QString& modelName)
{
    if (!d_ptr->isInitialized())
        d_ptr->initialize();

    QString strQuery = QString("SELECT brand_name FROM %1 WHERE model_name=\"%2\"").arg(Jcr::Database::BRAND_MODEL_TABLE).arg(modelName);
    QSqlQuery query = d_ptr->database.exec(strQuery);
    QString name;
    while (query.next())
        name = query.value(0).toString();
    return name;
}

bool DatabaseManager::isBrandName(const QString &brandName)
{
    if (!d_ptr->isInitialized())
        d_ptr->initialize();

    QString strQuery = QString("SELECT COUNT(*) FROM %1 WHERE brand_name=\"%2\"").arg(Jcr::Database::BRAND_MODEL_TABLE).arg(brandName);
    QSqlQuery q;
    q.prepare(strQuery);
    q.exec();
    int count = 0;
    if (q.next())
        count = q.value(0).toInt();

    return count != 0;
}

bool DatabaseManager::modelParamsExists(const QString &model)
{
    if (!d_ptr->isInitialized())
        d_ptr->initialize();

    QString strQuery = QString("SELECT COUNT(*) FROM %1 WHERE model_name=\"%2\"").arg(Jcr::Database::MODEL_PARAMS_TABLE).arg(model);
    QSqlQuery q;
    q.prepare(strQuery);
    q.exec();
    int count = 0;
    while (q.next())
        count = q.value(0).toInt();
    return count != 0;
}

bool DatabaseManager::modelTrueVideoLinkExists(const QString& model)
{
    if (!d_ptr->isInitialized())
        d_ptr->initialize();

    QString strQuery = QString("SELECT COUNT(*) FROM %1 WHERE model_name=\"%2\"").arg(Jcr::Database::MODEL_VIDEOLINK_REAL_TABLE).arg(model);
    QSqlQuery q;
    q.prepare(strQuery);
    q.exec();
    int count = 0;
    while (q.next())
        count = q.value(0).toInt();
    return count != 0;
}

} // namespace Jcr
